﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;

namespace CURDBase
{
    class CURDBaseForHelper
    {
        // 每个方法在调用时都建立一个连接，这样不会拥堵


        // 我们直接操作CURDBaseForHelper实现SQL命令， 
        // 而CURDBaseForHelper则是通过SQLHelperBase实现的
        public void Insert()
        {
            string sql = "insert into Employee([EmployeeName], [Gender], [NowAddress], [IdCard], [Tel], [WeChat], [OtherWork], [EntryDate], [PostId], [DepartmentId])";
            sql += " values('helper','男','北京','372321199911110066','18255463066','thinger66','暂无','2025-05-02',11,12)";
            int result = 0;
            try
            {
                result = SQLHelperBase.ExecuteNonQuery(sql);    
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);  
            }
            Console.WriteLine("受影响的行数："+result);  // 该行100%被执行，因为上面对异常做了处理，不会中断
        }

        public void UpData()
        {
            string sql = "update Employee set OtherWork='UI设计' where EmployeeName='win11'";
            int result = 0;
            try
            {
                result = SQLHelperBase.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("受影响的行数："+result);
        }

        public void Delete()
        {
            string sql = "delete Employee where EmployeeName='kd'";
            int result = 0;
            try
            {
                result = SQLHelperBase.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("受影响的行数："+result);
        }

        public void GetSingleResule()
        {
            string sql = "select count(*) from Employee";
            Object ob=null;     // 这里必须明确写出null
            try
            {
                ob = SQLHelperBase.ExecuteScalar(sql);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("单一结果："+ob);
        }

        public void GetResult()
        {
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            SqlDataReader reader = null;
            try
            {
                reader = SQLHelperBase.ExecuteReader(sql);
                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            while (reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}\t{reader[1]}\t{reader["IdCard"]}\t" +
                $"{reader["Tel"]}");
            }
           
            reader.Close();
        }

        public void GetSet1()
        {
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            DataSet ds = null;
            try
            {
                ds = SQLHelperBase.GetDataSet(sql);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t{row["IdCard"]}\t{row["Tel"]}");
            }
        }

        public void GetSet2()
        {
            string sql1 = "select EmployeeName,Gender,IdCard,Tel from Employee";
            string sql2 = "select PostId,PostName from Post";
            string sql3 = "select DepartmentId,DepartmentName from Department";

            Dictionary<string, string> dic = new Dictionary<string, string>()
            {
                ["Employee"] = sql1,
                ["Post"] = sql2,
                ["Department"] = sql3
            };

            DataSet ds = null;

            try
            {
                ds = SQLHelperBase.GetDataSet(dic);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            foreach (DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t" +
                    $"{row["IdCard"]}\t{row["Tel"]}");
            }
            Console.WriteLine("*************************************");
            foreach (DataRow row in ds.Tables["Post"].Rows)
            {
                Console.WriteLine($"{row["PostId"]}\t{row["PostName"]}");
            }
            Console.WriteLine("*************************************");
            foreach (DataRow row in ds.Tables["Department"].Rows)
            {
                Console.WriteLine($"{row["DepartmentId"]}\t{row["DepartmentName"]}");
            }

        }

    }
}
